package com.geekbang.work2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author nhsoft.lul
 */
public class ShardingSphereProxyApplication {

    static String URL = "jdbc:mysql://127.0.0.1:3307/sharding_db?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false";
    static String USER = "root";
    static String PASSWORD = "root";

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            String sql = "DROP TABLE IF EXISTS `book_order`;";
            statement = conn.prepareStatement(sql);
            statement.execute();

            sql = "CREATE TABLE `book_order` (" +
                    "  `order_id` bigint NOT NULL COMMENT '订单主键'," +
                    "  `customer_id` bigint not NULL comment '用户主键'," +
                    "  `order_total_money` decimal(16,4) DEFAULT NULL comment '单据总金额'," +
                    "  `order_discount_money` decimal(16,4) DEFAULT NULL comment '折扣金额'," +
                    "  `order_pay_money` decimal(16,4) DEFAULT NULL comment '支付金额'," +
                    "  `order_pay_type`  varchar(50) DEFAULT NULL comment '支付方式'," +
                    "  `create_at` datetime not NULL comment '创建时间'," +
                    "  `update_at` datetime default NULL comment '更新时间'," +
                    "  `pay_at` datetime default NULL comment '支付时间'," +
                    "  PRIMARY KEY (`order_id`)" +
                    ")  ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4;";
            statement = conn.prepareStatement(sql);
            statement.execute();

            sql = "insert into book_order(customer_id, create_at) values (?, now());";
            statement = conn.prepareStatement(sql);
            for(int i = 0 ; i < 100 ; i++){
                statement.setInt(1, i + 1);
                statement.addBatch();
            }
            statement.executeBatch();

            sql = "select * from book_order where customer_id = 1";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println(resultSet.getLong(1));
            }

            sql = "select count(*) from book_order";
            resultSet = statement.executeQuery(sql);
            resultSet.next();
            System.out.println(resultSet.getInt(1));

            sql = "delete from book_order";
            statement = conn.prepareStatement(sql);
            statement.execute();

            sql = "select count(*) from book_order";
            resultSet = statement.executeQuery(sql);
            resultSet.next();
            System.out.println(resultSet.getInt(1));

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
